﻿using System;
using System.Collections.Generic;
using EnglishLibrary;
using Helper;

namespace DataAccess
{
    public class CategoryDAO
    {
        private string _filePath;

        public string FilePath
        {
            get { return _filePath; }
            set { _filePath = value; }
        }

        public CategoryDAO(string profilePath)
        {
            _filePath = profilePath;
        }

        public CategoryDAO()
        {
            _filePath = string.Empty;
        }

        #region Get items
        public List<Category> GetListCategories()
        {
            var list = new List<Category>();
            const string query = "SELECT CATEGORYID, NAME, DESCRIPTION, CREATEDDATE FROM CATEGORY";

            try
            {
                DataProvider.OpenConnection(_filePath);
                var reader = DataProvider.ExecuteReader(query);

                while (reader.Read())
                {
                    var cateItem = new Category();

                    if (!reader.IsDBNull(0))
                        cateItem.CategoryID = reader.GetInt32(0);
                    if (!reader.IsDBNull(1))
                        cateItem.Name = reader.GetString(1);
                    if (!reader.IsDBNull(2))
                        cateItem.Description = reader.GetString(2);
                    if (!reader.IsDBNull(3))
                        cateItem.CreatedDate = reader.GetDateTime(3);

                    list.Add(cateItem);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                DataProvider.CloseConnection();
            }
            return list;
        }
        public Category GetCateByName(string name)
        {
            Category cateItem = null;

            try
            {
                DataProvider.OpenConnection(_filePath);
                var query = "SELECT CATEGORYID, NAME, DESCRIPTION, CREATEDDATE " +
                               "FROM CATEGORY " +
                               "WHERE NAME LIKE '" + name + "'";

                var reader = DataProvider.ExecuteReader(query);

                if (reader.Read())
                {
                    cateItem = new Category();

                    if (!reader.IsDBNull(0))
                        cateItem.CategoryID = reader.GetInt32(0);
                    if (!reader.IsDBNull(1))
                        cateItem.Name = reader.GetString(1);
                    if (!reader.IsDBNull(2))
                        cateItem.Description = reader.GetString(2);
                    if (!reader.IsDBNull(3))
                        cateItem.CreatedDate = reader.GetDateTime(3);
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                DataProvider.CloseConnection();
            }

            return cateItem;
        }
        public Category GetCateById(int id)
        {
            Category cateItem = null;

            try
            {
                DataProvider.OpenConnection(_filePath);
                var query = "SELECT CATEGORYID, NAME, DESCRIPTION, CREATEDDATE " +
                               "FROM CATEGORY " +
                               "WHERE CATEGORYID = " + id;

                var reader = DataProvider.ExecuteReader(query);

                if (reader.Read())
                {
                    cateItem = new Category();

                    if (!reader.IsDBNull(0))
                        cateItem.CategoryID = reader.GetInt32(0);
                    if (!reader.IsDBNull(1))
                        cateItem.Name = reader.GetString(1);
                    if (!reader.IsDBNull(2))
                        cateItem.Description = reader.GetString(2);
                    if (!reader.IsDBNull(3))
                        cateItem.CreatedDate = reader.GetDateTime(3);
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                DataProvider.CloseConnection();
            }

            return cateItem;
        }
        #endregion

        #region Insert Items
        public bool InsertItem(Category cateItem)
        {
            var query = String.Format("INSERT INTO CATEGORY(NAME, DESCRIPTION, CREATEDDATE) " +
                                      "VALUES('{0}', '{1}', '{2}')", 
                                      QueryHelper.Validate(cateItem.Name), 
                                      QueryHelper.Validate(cateItem.Description), 
                                      cateItem.CreatedDate);

            try
            {
                DataProvider.OpenConnection(_filePath);
                DataProvider.ExecuteNonquery(query);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                DataProvider.CloseConnection();
            }
            return true;
        }
        #endregion
    }
}
